﻿CREATE PROCEDURE [dbo].[select_deal_unclear]
	@in_customer nvarchar(20)
AS
	declare @result_table table(result_id bigint identity(1,1) primary key,
								result_code nvarchar(20) not null,
								result_store money default(0) not null,
								result_others money default(0) not null,
								result_total money default(0) not null,
								result_rest money default(0) not null,
								result_date smalldatetime not null,
								result_memo nvarchar(20) not null);
	
	declare @code nvarchar(20);
	declare @store money;
	declare @others money;
	declare @total money;
	declare @rest money;
	declare @date smalldatetime;
	declare @memo nvarchar(20);							

	declare @weight_in decimal(5,1);
	declare @weight_out decimal(5,1);
	
	declare deal_cursor cursor for
	select deal_code, deal_store, deal_others, deal_total,
	deal_rest, deal_date, deal_memo from tb_deal 
	where deal_customer = @in_customer and deal_rest != 0;
	open deal_cursor;
	fetch next from deal_cursor into @code, @store, @others, @total, @rest, @date, @memo;
	while @@fetch_status = 0 begin
	
		select @weight_in = isnull(sum(inout_in),0) from tb_inout
		where inout_customer = @in_customer and inout_code = @code;
		
		select @weight_out = isnull(sum(inout_out),0) from tb_inout
		where inout_customer = @in_customer and inout_code = @code;
		
		if (@weight_in = @weight_out)
		
			insert into @result_table(result_code, result_store, result_others,
			result_total, result_rest, result_date, result_memo) values (
			@code, @store, @others, @total, @rest, @date, @memo);
		
		fetch next from deal_cursor into @code, @store, @others, @total, @rest, @date, @memo;
	end
	
	select * from @result_table;